﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
using Microsoft.Win32;


namespace WpfApplication2
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : System.Windows.Window
    {

        public static string ConnectionString { get; set; }
        public static string cnnStr { get; set; }
        public static List<ExcelProperty> excelList = new List<ExcelProperty>();
        ExcelProperty property = new ExcelProperty();
        public static int count { get; set; }

        public MainWindow()
        {
            InitializeComponent();
            count = 1;
            this.Closing += new System.ComponentModel.CancelEventHandler(MainWindow_Closing);
        }
        
        private void MainWindow_Closing(object sender, System.ComponentModel.CancelEventArgs e)
        {
            ApplicationClose();
        }

        private void ApplicationClose()
        {
            System.Windows.Application.Current.Shutdown();
        }

        public void DBcomboBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (this.DBcomboBox.SelectedValue != null && this.DBcomboBox.SelectedValue.ToString() != "Please select database...")
            {
                cnnStr = " initial catalog = " + DBcomboBox.SelectedValue + ";";
                cnnStr = ConnectionString + cnnStr;
            }
            btnAddSheet.IsEnabled = true;
        }

        private void Plus_ImageFailed(object sender, ExceptionRoutedEventArgs e)
        {

        }

        private void btnSave_Click(object sender, RoutedEventArgs e)
        {
            if (txtBoxQuery.Text.ToLower().Contains("update"))
            {
                MessageBox.Show("Update statements are not allowed.");
            }
            else if (txtBoxQuery.Text.ToLower().Contains("truncate"))
            {
                MessageBox.Show("Truncate statements are not allowed.");
            }
            else if (txtBoxQuery.Text.ToLower().Contains("delete"))
            {
                MessageBox.Show("Delete statements are not allowed.");
            }
            else if (txtBoxQuery.Text.ToLower().Contains("insert"))
            {
                MessageBox.Show("Insert statements are not allowed.");
            }
            else if (txtBoxQuery.Text.Trim() == "")
            {
                MessageBox.Show("Please write query.");
            }
            //else if (txtBoxQuery.Text.ToLower().Contains("select") && !txtBoxQuery.Text.ToLower().Contains("from"))
            //{
            //    MessageBox.Show("Please write proper query.");
            //}
            //else if (!txtBoxQuery.Text.ToLower().Contains("select") && txtBoxQuery.Text.ToLower().Contains("from"))
            //{
            //    MessageBox.Show("Please write proper query.");
            //}
            else
            {
                ExcelProperty excel = new ExcelProperty();
                //Remove original item
                lblEdit.Visibility = System.Windows.Visibility.Visible;
                comboBoxEdit.Visibility = System.Windows.Visibility.Visible;
                comboBoxEdit.IsEnabled = true;
                excelList.Remove(property);
                excel.SheetName = txtBoxSheetName.Text == "" ? "Sheet" + (count - 1).ToString() : txtBoxSheetName.Text;
                excel.Query = txtBoxQuery.Text;
                excelList.Add(excel);
                comboBoxEdit.ItemsSource = null;
                this.comboBoxEdit.ItemsSource = excelList;

                this.comboBoxEdit.DisplayMemberPath = "SheetName";
                this.comboBoxEdit.SelectedValuePath = "Query";
                txtBoxQuery.Text = null;
                txtBoxSheetName.Text = null;
                txtBoxQuery.IsEnabled = false;
                txtBoxSheetName.IsEnabled = false;
                btnSave.IsEnabled = false;
                btnCancel.IsEnabled = false;
                MessageBox.Show("Saved Successfully");
                this.comboBoxEdit.SelectedIndex = 0;
                if (btnCreateExcel.IsEnabled == false)
                {
                    btnCreateExcel.IsEnabled = true;
                }
            }
        }

        private void comboBoxEdit_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            property = this.comboBoxEdit.SelectedItem as ExcelProperty;

            if (comboBoxEdit.ItemsSource != null && comboBoxEdit.SelectedIndex != 0 && property != null && property.SheetName != "Edit Saved Excel Sheet...")
            {
                btnAddSheet.IsEnabled = false;
                comboBoxEdit.IsEnabled = false;
                txtBoxSheetName.Text = property.SheetName;
                txtBoxQuery.Text = property.Query;
                txtBoxQuery.IsEnabled = true;
                txtBoxSheetName.IsEnabled = true;
                btnSave.IsEnabled = true;
                btnCancel.IsEnabled = true;
                btnCreateExcel.IsEnabled = false;
            }
            else
            {
                btnAddSheet.IsEnabled = true;
                txtBoxQuery.Text = null;
                txtBoxSheetName.Text = null;
                txtBoxQuery.IsEnabled = false;
                txtBoxSheetName.IsEnabled = false;
                btnSave.IsEnabled = false;
                btnCancel.IsEnabled = false;
                comboBoxEdit.IsEnabled = true;
            }
            //btnCreateExcel.IsEnabled = false;
        }

        private void btnCreateExcel_Click(object sender, RoutedEventArgs e)
        {
            MainProcess process = new MainProcess();
            ExcelOperation excel = new ExcelOperation();
            Workbook wb = process.CreateWorkBook();
            ExcelProperty property = new ExcelProperty();
            List<ExcelProperty> list = new List<ExcelProperty>();
            try
            {
                count = 1;
                Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
                dlg.DefaultExt = ".xlsx";
                dlg.Filter = "Excel documents (.xlsx)|*.xlsx";
               

                list = excelList.OrderByDescending(x => x.SheetName).ToList();
                foreach (var exc in list)
                {
                    if (exc.SheetName != "Edit Saved Excel Sheet...")
                    {
                        process.PerformOperation(wb, exc.Query, exc.SheetName, cnnStr);
                    }
                }
                
                Nullable<bool> result = dlg.ShowDialog();
                string filename = string.Empty;
                if (result == true)
                {
                    filename = dlg.FileName;
                }
                excel.SaveExcel(wb, filename);

                txtBoxQuery.Text = null;
                txtBoxSheetName.Text = null;
                txtBoxQuery.IsEnabled = false;
                txtBoxSheetName.IsEnabled = false;
                btnSave.IsEnabled = false;
                btnCreateExcel.IsEnabled = false;
                comboBoxEdit.Visibility = System.Windows.Visibility.Hidden;
                excelList = new List<ExcelProperty>();
                lblEdit.Visibility = System.Windows.Visibility.Hidden;
                //btnViewExcel.Visibility = System.Windows.Visibility.Visible;
                MessageBox.Show("Excel Created Successfully !");
            }
            catch (Exception exec)
            {

                MessageBox.Show(exec.Message.ToString()); ;
            }
        }

        //private void btnViewExcel_Click(object sender, RoutedEventArgs e)
        //{
        //    OpenFileDialog ofd = new OpenFileDialog();
        //    ofd.Filter = "Excel files|*.xls;*.xlsx";
        //    if (ofd.ShowDialog() == true)
        //    {
        //        string strFilePath = ofd.FileName;
        //        System.Diagnostics.Process.Start(strFilePath);
        //    }

        //}

        private void btnAddSheet_Click(object sender, RoutedEventArgs e)
        {
            
            if (DBcomboBox.SelectedIndex == 0)
            {
                MessageBox.Show("Please Select Database");
            }
            else
            {
                btnAddSheet.IsEnabled = false;
                txtBoxSheetName.IsEnabled = true;
                txtBoxQuery.IsEnabled = true;
                btnSave.IsEnabled = true;
                btnCancel.IsEnabled = true;
                txtBoxQuery.Text = "";
                txtBoxSheetName.Text = "Sheet"+count.ToString();
                txtBoxQuery.Focus();
                ExcelProperty newProperty = new ExcelProperty();
                newProperty.SheetName = "Edit Saved Excel Sheet...";
                if (comboBoxEdit.SelectedValue != null && comboBoxEdit.SelectedValue.ToString() != newProperty.SheetName)
                {
                    comboBoxEdit.SelectedValue = newProperty.SheetName;
                }
                txtBoxSheetName.Focus();
                btnAddSheet.Visibility = System.Windows.Visibility.Visible;
                count++;
            }
        }

        private void btnCancel_Click(object sender, RoutedEventArgs e)
        {
            txtBoxQuery.Text = null;
            txtBoxSheetName.Text = null;
            txtBoxQuery.IsEnabled = false;
            txtBoxSheetName.IsEnabled = false;
            btnSave.IsEnabled = false;
            btnCancel.IsEnabled = false;
            btnAddSheet.IsEnabled = true;
            if (excelList.Count > 1)
            {
                btnCreateExcel.IsEnabled = true;
            }
            comboBoxEdit.SelectedIndex = 0;
        }

        private void Connect_Click(object sender, RoutedEventArgs e)
        {
            
            DBConnection dbCon = new DBConnection(this);
            DBcomboBox.IsEnabled = true;
            dbCon.rdbtnLogin.IsChecked = true;
            lblEdit.Visibility = System.Windows.Visibility.Hidden;
            comboBoxEdit.Visibility = System.Windows.Visibility.Hidden;
            txtBoxSheetName.Text = null;
            txtBoxQuery.Text = null;
            txtBoxQuery.IsEnabled = false;
            txtBoxSheetName.IsEnabled = false;
            excelList = new List<ExcelProperty>();
            ExcelProperty property = new ExcelProperty();
            property.SheetName = "Edit Saved Excel Sheet...";
            excelList.Add(property);
            dbCon.ShowDialog();
        }

        private void Exit_Click(object sender, RoutedEventArgs e)
        {
            ApplicationClose();
        }
    }
}
